﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using QAToolsAddin.Components;
using QAToolsAddin.GenericDialogs;
using Excel = Microsoft.Office.Interop.Excel;

namespace QAToolsAddin.CustomDialogs {
    public partial class FrmImport : ExcelForm {
        private readonly Excel.Application _excelapp;
        private Excel.Workbook _workbook;
        private Excel.Worksheet _worksheet;
        private Excel.Workbook _wbTemplate;
        private readonly ExcelRefEdit[] _ctrlAddress;
        private readonly object Missing = Type.Missing;

        public string DefaultDir { get; set; }

        public FrmImport(Excel.Application excelapp, CultureInfo culture)
            : base(excelapp) {
            _excelapp = excelapp;
            InitializeComponent();
            _ctrlAddress = new[] { ctrlAddress1, ctrlAddress2, ctrlAddress3, ctrlAddress4, ctrlAddress5, ctrlAddress6 };
        }

        private void LoadSettings() {
            DefaultDir = (string)Settings.GetParam(this, "DefaultDir", Environment.GetFolderPath(Environment.SpecialFolder.Personal));
            ctrlCurrentSheet.Checked = (bool)Settings.GetParam(this, "ctrlCurrentSheet", true);
            ctrlNewSheet.Checked = (bool)Settings.GetParam(this, "ctrlNewSheet", false);
            ctrlInteriorColor.SelectedColor = (int)Settings.GetParam(this, "ctrlInteriorColor", 0xFF);
            ctrlFontColor.SelectedColor = (int)Settings.GetParam(this, "ctrlFontColor", 0xFFFFFF);
        }

        private void SaveSettings() {
            Settings.SetParam(this, "DefaultDir", DefaultDir);
            Settings.SetParam(this, "ctrlCurrentSheet", ctrlCurrentSheet.Checked);
            Settings.SetParam(this, "ctrlNewSheet", ctrlNewSheet.Checked);
            Settings.SetParam(this, "ctrlInteriorColor", ctrlInteriorColor.SelectedColor);
            Settings.SetParam(this, "ctrlFontColor", ctrlFontColor.SelectedColor);
        }

        private void FrmImport_Load(object sender, EventArgs e) {
            _workbook = _excelapp.ActiveWorkbook;
            _worksheet = (Excel.Worksheet)_excelapp.ActiveSheet;
            if (_workbook == null || _worksheet == null)
                throw new InputError("Open a workbook first!");
            LoadSettings();
        }

        private void FrmImport_FormClosing(object sender, FormClosingEventArgs e) {
            DisposeWorkbookList();
        }

        private void DisposeWorkbookList() {
            SuspendLayout();
            Utils.DisposeWorkbook(ref _wbTemplate);
            ctrlNames.Enabled = false;
            ctrlNames.Clear();
            foreach (ExcelRefEdit refedit in _ctrlAddress) {
                refedit.Enabled = false;
                refedit.Clear();
            }
            ctrlListWorkbook.Items.Clear();
            ResumeLayout();
        }

        private void SetTempateWorkbook(FileInfo file) {
            if (file == null || (_wbTemplate != null && file.FullName == _wbTemplate.FullName))
                return;
            SuspendLayout();
            Utils.DisposeWorkbook(ref _wbTemplate);
            _workbook.Saved = false;
            _wbTemplate = _excelapp.Workbooks.Open(file.FullName, false, true, FileShare.Read, Type.Missing, Type.Missing, false, Excel.XlPlatform.xlWindows, Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            ctrlNames.Enabled = true;
            ctrlNames.Names = _wbTemplate.Names;
            //ctrlNames.CheckAll();
            foreach (ExcelRefEdit refedit in _ctrlAddress) {
                refedit.Enabled = true;
                refedit.SwapWorkbook(_wbTemplate);
            }
            ResumeLayout();
        }

        private void ctrlListWorkbook_SelectedIndexChanged(object sender, EventArgs e) {
            try {
                _excelapp.ScreenUpdating = false;
                SetTempateWorkbook((FileInfo)ctrlListWorkbook.SelectedItem);
            } catch (InputError ex) {
                MsgBox.ShowError(_excelapp, ex.Message);
            } finally {
                _excelapp.ScreenUpdating = true;
            }
        }

        private void ctrlNames_Enter(object sender, EventArgs e) {
            if (_wbTemplate != null)
                ((Excel._Workbook)_wbTemplate).Activate();
        }

        private void btAdd_Click(object sender, EventArgs e) {
            List<FileInfo> files = SelectFiles();
            if (files != null) {
                FileInfo[] filesArray = files.ToArray();
                ctrlListWorkbook.Items.AddRange(filesArray);
            }
        }

        private List<FileInfo> SelectFiles() {
            using (var dlgOpen = new OpenFileDialog()) {
                dlgOpen.Title = "Excel files selection";
                dlgOpen.ShowReadOnly = true;
                dlgOpen.Multiselect = true;
                dlgOpen.AutoUpgradeEnabled = false;
                dlgOpen.RestoreDirectory = true;
                dlgOpen.Filter = "Excel Workbook (*.xls,*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*";
                dlgOpen.InitialDirectory = DefaultDir;
                if (dlgOpen.ShowDialog() == DialogResult.OK) {
                    DefaultDir = new FileInfo(dlgOpen.FileNames[0]).Directory.FullName;
                    var files = new List<FileInfo>(dlgOpen.FileNames.Length);
                    foreach (string file in dlgOpen.FileNames)
                        files.Add(new FileInfo(file));
                    return files;
                } else {
                    return null;
                }
            }
        }

        private void btClose_Click(object sender, EventArgs e) {
            Close();
        }

        private void btClear_Click(object sender, EventArgs e) {
            ctrlListWorkbook.Items.Clear();
            DisposeWorkbookList();
        }

        private void btImport_Click(object sender, EventArgs e) {
            try {
                Utils.SwitchExcelToIdle(_excelapp, false);
                RunImport();
            } catch (InputError ex) {
                MsgBox.ShowError(ExcelApp, ex.Message);
            } finally {
                Utils.SwitchExcelToIdle(_excelapp, true);
            }
        }

        private void RunImport() {
            if (ctrlListWorkbook.Items.Count == 0)
                throw new InputError("List of workbook is empty.");

            var names = new List<string>();
            foreach (Excel.Name name in ctrlNames.CheckedNames)
                names.Add(name.Name);

            var addresses = new List<KeyValuePair<string, string>>();
            foreach (ExcelRefEdit refedit in _ctrlAddress) {
                if (refedit.IsEmpty == false) {
                    foreach (Excel.Range area in refedit.Range)
                        addresses.Add(new KeyValuePair<string, string>(area.Worksheet.Name, area.Address.Replace("$", "")));
                }
            }

            var files = new List<FileInfo>(ctrlListWorkbook.Items.Count);
            foreach (FileInfo file in ctrlListWorkbook.Items)
                files.Add(file);

            if (names.Count == 0 && addresses.Count == 0)
                throw new InputError("At leas one name or reference is required.");

            ResultSet res = GetDatasFromNamesAndAddresses(files, names, addresses);


            Excel.Worksheet worksheet;
            if (ctrlNewSheet.Checked)
                worksheet = (Excel.Worksheet)_workbook.Sheets.Add(Missing, _workbook.Sheets[_workbook.Sheets.Count], Missing, Missing);
            else
                worksheet = _worksheet;

            Utils.AddTitlesToRange((Excel.Range)worksheet.Cells[1, 1], res.Titles);
            Utils.AddDataToRange((Excel.Range)worksheet.Cells[2, 1], res.Data);
            Utils.AddTagsToRange((Excel.Range)worksheet.Cells[2, 1], res.Tag, ctrlInteriorColor.SelectedColor, ctrlFontColor.SelectedColor);

            ((Excel._Workbook)worksheet.Parent).Activate();
            ((Excel._Worksheet)worksheet).Activate();

            SaveSettings();
        }

        private ResultSet GetDatasFromNamesAndAddresses(IList<FileInfo> files, ICollection<string> names, List<KeyValuePair<string, string>> addresses) {
            int nbRow = files.Count;
            int nbCol = names.Count + addresses.Count + 1;
            var res = new ResultSet {
                Data = ExcelArray<object>.Create(nbRow, nbCol),
                Tag = ExcelArray<bool>.Create(nbRow, nbCol),
                Titles = ExcelArray<object>.Create(1, nbCol)
            };

            res.Titles[1, 1] = "Workbook";
            int ci = 1;
            foreach (string t in names)
                res.Titles[1, ++ci] = t;
            foreach (KeyValuePair<string, string> t in addresses) {
                if (Regex.IsMatch(t.Key, @"^\w+$"))
                    res.Titles[1, ++ci] = t.Key + "!" + t.Value;
                else
                    res.Titles[1, ++ci] = "''" + t.Key + "'!" + t.Value;
            }
            for (int r = 0, ri = 1; r < nbRow; r++, ri++) {
                Excel.Workbook wb = null;
                FileInfo tempFile = null;
                try {
                    res.Data[ri, 1] = files[r].Name;
                    tempFile = new FileInfo(Path.Combine(Path.GetTempPath(), "excel_import" + r.ToString() + files[r].Extension));
                    File.Copy(files[r].FullName, tempFile.FullName, true);
                    wb = _excelapp.Workbooks.Open(tempFile.FullName, false, true, FileShare.Read, Type.Missing, Type.Missing, false, Excel.XlPlatform.xlWindows, Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    ci = 1;
                    foreach (string t in names) {
                        ci++;
                        try {
                            Excel.Name name = wb.Names.Item(t, Type.Missing, Type.Missing);
                            try {
                                Excel.Range nameRange = name.RefersToRange;
                                res.Data[ri, ci] = ((Excel.Range)nameRange.Cells[1, 1]).Value;
                            } catch {
                                res.Data[ri, ci] = "#ERROR!";
                                res.Tag[ri, ci] = true;
                            }
                        } catch {
                            res.Data[ri, ci] = "#NAME!";
                            res.Tag[ri, ci] = true;
                        }
                    }
                    foreach (KeyValuePair<string, string> t in addresses) {
                        ci++;
                        try {
                            var ws = (Excel.Worksheet)wb.Worksheets[t.Key];
                            try {
                                res.Data[ri, ci] = ws.Range[t.Value].Value;
                            } catch {
                                res.Data[ri, ci] = "#ERROR!";
                                res.Tag[ri, ci] = true;
                            }
                        } catch {
                            res.Data[ri, ci] = "#SHEET!";
                            res.Tag[ri, ci] = true;
                        }
                    }
                } catch {
                    for (int j = 2; j <= nbCol; j++) {
                        res.Data[ri, j] = "#OPEN!";
                        res.Tag[ri, j] = true;
                    }
                } finally {
                    if (wb != null) {
                        wb.Close(false, Type.Missing, Type.Missing);
                        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb);
                    }
                    if (tempFile != null && tempFile.Exists)
                        tempFile.Delete();
                }
            }
            return res;
        }

        private void ctrlNames_SelectedIndexChanged(object sender, EventArgs e) {
            try {
                Excel.Range range = ctrlNames.SelectedName.RefersToRange;
                ((Excel._Worksheet)range.Worksheet).Activate();
                range.Activate();
            } catch { }
        }

        //private void ctrlRef_Escape(object sender, EventArgs e)
        //{
        //    btClose.Focus();
        //}

        private void ctrlCurrentSheet_MouseDown(object sender, MouseEventArgs e) {
            if (_workbook != null && _excelapp.ActiveWorkbook != _workbook)
                ((Excel._Workbook)_workbook).Activate();
            if (_worksheet != null && _excelapp.ActiveSheet != _worksheet)
                ((Excel._Worksheet)_worksheet).Activate();
        }

        private void ctrlAddress_Escape(object sender, EventArgs e) {
            btClose.Focus();
        }

        private class ResultSet {
            public object[,] Data;
            public object[,] Titles;
            public bool[,] Tag;
        }

    }
}
